/**
 * 物料相关功能
 */
import { mysql } from 'src-ssr/db/connect';
import TableHelper from 'src-ssr/db/tableHelper';
import * as tools from 'src-ssr/util/common';
import logs from 'src-ssr/util/logs';
import { Sequelize, Op, QueryTypes } from 'sequelize';
import models, { sequelize } from 'src-ssr/db/sequelize';
import moment from 'moment';
import autocode from 'src-ssr/util/autocode';
import pinyin from 'src/lib/vue-py';

import * as singleUtils from 'src-ssr/controller/common/singleOperate';
import * as mdUtils from 'src-ssr/controller/common/masterDetailOperate';

import listConfig from 'app/search/tenant/material/list.js';
import summaryConfig from 'app/search/tenant/material/summary.js';

const menuName = 'material';
const modelName = 't_materials';
const modelLabel = '货物档案';

// 取数据
async function fetchData ({ startRow, count, filter, sortBy, descending, user }) {
  const where = await tools.assemblyWhere({
    filter,
    searchConfig: listConfig.search,
    fn: ({ where, field, search }) => {
    }
  });
  where.tenant_id = user.tid; // 只取该租户的数据
  if (filter.catalog_id) {
    where[Op.or] = [
      { catalog_id1: filter.catalog_id },
      { catalog_id2: filter.catalog_id },
      { catalog_id3: filter.catalog_id },
      { catalog_id4: filter.catalog_id }]
  }

  const exclude = ['uid', 'history', 'images'];
  // 只取单条数据
  if (filter && filter.id) {
    where.id = filter.id;
    exclude.splice(exclude.indexOf('history'), 1);
    exclude.splice(exclude.indexOf('images'), 1);
  }

  const order = sortBy ? [[sortBy, descending ? 'DESC' : 'ASC']] : [];

  let right = { data: true };
  if (user) {
    right = await tools.checkUserRight(user, menuName, ['data']);
    if (!right.data) { // 是否有查看全部的权限
      where.create_user_id = user.id;
    }
  }

  let ret = { rows: [], count: 0 };
  try {
    ret = await models[modelName].findAndCountAll({
      attributes: {
        exclude
      },
      include: [{
        model: models.t_material_catalog,
        attributes: [['catalog_name', 'catalog_name1']],
        as: 'catalog1'
      }, {
        model: models.t_material_catalog,
        attributes: [['catalog_name', 'catalog_name2']],
        as: 'catalog2'
      }, {
        model: models.t_material_catalog,
        attributes: [['catalog_name', 'catalog_name3']],
        as: 'catalog3'
      }, {
        model: models.t_material_catalog,
        attributes: [['catalog_name', 'catalog_name4']],
        as: 'catalog4'
      }],
      where,
      order,
      offset: count ? startRow : undefined,
      limit: count || undefined,
      group: count ? [`${modelName}.id`, 'catalog1.tenant_id', 'catalog2.tenant_id', 'catalog3.tenant_id', 'catalog4.tenant_id'] : undefined
    });
  } catch (e) {
    console.error(modelLabel + '列表出错', e);
  }

  // 处理其它数据
  const rows = [];
  ret.rows.forEach(row => {
    const jsonRow = row.toJSON();
    tools.flatDetail(['catalog1', 'catalog2', 'catalog3', 'catalog4'], jsonRow); // 拉平信息

    // 数量转数值
    for (const key of ['sale_price']) {
      if (jsonRow[key]) jsonRow[key] = Number(jsonRow[key]);
    }

    if (user) jsonRow._owner = row.create_user_id == user.id; // 是否为拥有者
    rows.push(jsonRow);
  });

  if (filter && filter.id) {
    if (rows.length > 0) {
      return rows[0];
    } else {
      return {};
    }
  } else {
    return {
      data: rows,
      rowsNumber: typeof ret.count === 'object' ? ret.count.length : ret.count
    }
  }
}

// 列表
export const list = async (req, res) => {
  const { startRow, count, filter, sortBy, descending } = req.body;
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);

  const ret = await fetchData({ startRow, count, filter, sortBy, descending, user });
  res.json(ret);
};

// 详细信息
export const detail = async (req, res) => {
  const { id } = req.body;

  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  const ret = await fetchData({ filter: { id }, user });

  res.json(ret);
};

// 生成物料编码
async function createMaterialNo ({ catalog_id1, catalog_id2, catalog_id3, catalog_id4, material_name }, user) {
  const cataArr = [catalog_id1 || null, catalog_id2 || null, catalog_id3 || null, catalog_id4 || null];

  // 编码规则
  let codeRule = {
    prefix_type: 'fixed', // 编码类型：fixed-固定前缀 dynamic-物料名称首字母
    prefix: 'MDY', // 固定前缀
    rule: ['1', '2', '3', '4'], // 固定前缀时，包含分类编码的层级
    digits: 4 // 流水号长度
  };

  // 取分类
  const catalogs = await models.t_material_catalog.findAll({
    attributes: ['id', 'catalog_code', 'code_rule'],
    where: {
      tenant_id: user.tid,
      id: {
        [Op.in]: cataArr
      }
    },
    raw: true
  });

  // 取自定义编码规则
  for (const id of [catalog_id4, catalog_id3, catalog_id2, catalog_id1]) {
    const catalog = catalogs.find(v => v.id == id);
    if (catalog && catalog.code_rule) {
      codeRule = catalog.code_rule;
      break;
    }
  }

  let customPrefix = '';
  if (codeRule.prefix_type === 'fixed') {
    // 生成自定义编码
    customPrefix = codeRule.prefix || '';
    codeRule.rule = codeRule.rule.map(v => Number(v)).sort((a, b) => a - b); // 排序
    for (const level of codeRule.rule) {
      const cata = catalogs.find(v => v.id == cataArr[level - 1]);
      customPrefix += cata ? cata.catalog_code : '00';
    }
  } else {
    customPrefix = pinyin.chineseToPinYin(material_name, true).toUpperCase() || '';
  }
  return await autocode.newCode({ tid: user.tid, code: 'MT', custom: customPrefix, digits: codeRule.digits });
}
/**
 * 新增
 */
export const create = async (req, res) => {
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);

  // 生成编码
  const { formData } = req.body;
  if (!formData.material_no) {
    // 编码规则
    /* let codeRule = {
      prefix_type: 'fixed', // 编码类型：fixed-固定前缀 dynamic-物料名称首字母
      prefix: 'MDY', // 固定前缀
      rule: ['1', '2', '3', '4'], // 固定前缀时，包含分类编码的层级
      digits: 4 // 流水号长度
    };
    const { catalog_id1, catalog_id2, catalog_id3, catalog_id4, material_name } = formData;
    const catalogs = await models.t_material_catalog.findAll({
      attributes: ['id', 'catalog_code', 'code_rule'],
      where: {
        tenant_id: user.tid,
        id: {
          [Op.in]: [catalog_id4, catalog_id3, catalog_id2, catalog_id1]
        }
      },
      raw: true
    });

    // 取自定义编码规则
    for (const id of [catalog_id4, catalog_id3, catalog_id2, catalog_id1]) {
      const catalog = catalogs.find(v => v.id == id);
      if (catalog && catalog.code_rule) {
        codeRule = catalog.code_rule;
        break;
      }
    }

    let customPrefix = '';
    if (codeRule.prefix_type === 'fixed') {
      // 生成自定义编码
      customPrefix = codeRule.prefix || '';
      codeRule.rule = codeRule.rule.map(v => Number(v)).sort((a, b) => a - b); // 排序
      for (const level of codeRule.rule) {
        const cata = catalogs.find(v => v.id == formData[`catalog_id${level}`]);
        customPrefix += cata ? cata.catalog_code : '00';
      }
    } else {
      customPrefix = pinyin.chineseToPinYin(material_name, true).toUpperCase() || '';
    }
    formData.material_no = await autocode.newCode({ tid: user.tid, code: 'MT', custom: customPrefix, digits: codeRule.digits }) */
    formData.material_no = await createMaterialNo(formData, user);
  }

  const ret = await singleUtils.createData(modelName, modelLabel, menuName, req, true);

  res.json(ret);
};

/**
 * 更新
 */
export const update = async (req, res) => {
  const ret = await singleUtils.updateData(modelName, modelLabel, menuName, req, true);
  res.json(ret);
};

/**
 * 删除产品
 */
export const destroy = async (req, res) => {
  // 删除前置检查
  const checkModels = [
    async (id) => {
      const [rows] = await mysql.query(`SELECT count(*) cnt
        FROM t_sale_details d
        INNER JOIN t_sales m ON d.master_uid=m.uid
        LEFT JOIN t_materials t ON d.material_uid=t.uid
        WHERE t.id=?`, [id]);
      if (rows[0].cnt > 0) {
        return `已被用于销售单${rows[0].cnt}次，不能删除`;
      } else {
        return null;
      }
    }
  ];

  const promises = checkModels.map(fn => fn(req.body.id));
  const results = await Promise.all(promises);

  // 处理结果
  const checkResult = results.filter(v => !!v);
  if (checkResult.length > 0) {
    return res.json({ result: 'fail', message: checkResult.join('<br>') });
  }

  const ret = await singleUtils.destroyData(modelName, modelLabel, menuName, req, true);

  res.json(ret);
};

// 统计
export const summary = async (req, res) => {
  const { startRow, count, filter, sortBy, descending, columns } = req.body;

  const where = await tools.assemblyWhere({
    filter,
    searchConfig: summaryConfig.search,
    raw: true,
    fn: ({ where, field, search }) => {
      if (field === 'catalog') {
        where.push(`(m.catalog_id1=${search} OR m.catalog_id2=${search} OR m.catalog_id3=${search} OR m.catalog_id4=${search})`);
      }
    }
  });

  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  const right = await tools.checkUserRight(user, menuName, ['data']);
  if (!right.data) { // 是否有查看全部的权限
    where.push(`m.create_user_id=${user.id}`);
  }
  where.push(`m.tenant_id=${user.tid}`);

  const order = [];
  if (['year', 'month', 'day'].includes(sortBy)) { // 日期型的一起排序
    if (columns.includes('year')) {
      order.push(`year ${descending ? 'DESC' : 'ASC'}`);
    }
    if (columns.includes('month')) {
      order.push(`month ${descending ? 'DESC' : 'ASC'}`);
    }
    if (columns.includes('day')) {
      order.push(`day ${descending ? 'DESC' : 'ASC'}`);
    }
  } else {
    sortBy && columns.includes(sortBy) && order.push(`${sortBy} ${descending ? 'DESC' : 'ASC'}`);
  }

  const limit = count ? `LIMIT ${startRow}, ${count}` : '';

  // group by 字段
  const groupField = columns.filter(v => ['year', 'month', 'day', 'create_user_name', 'material_name_model',
    'catalog_name1', 'catalog_name2', 'catalog_name3', 'catalog_name4'].includes(v)).join(', ');

  const allDataSql = `SELECT @idx:=@idx+1 AS id, ${groupField ? groupField + ',' : ''}
    COUNT(*) bill_count, SUM(sale_count) sale_count, SUM(sale_amount) sale_amount, SUM(sale_money) sale_money
    FROM (
      SELECT FROM_UNIXTIME(m.create_time, '%Y') year,
      FROM_UNIXTIME(m.create_time, '%m') month, FROM_UNIXTIME(m.create_time, '%d') day,
      m.create_user_name, m.material_name_model, c1.catalog_name catalog_name1,
      c2.catalog_name catalog_name2, c3.catalog_name catalog_name3, c4.catalog_name catalog_name4,
      (SELECT COUNT(id) FROM t_sale_details s WHERE m.uid=s.material_uid AND m.tenant_id=s.tenant_id) sale_count,
      (SELECT SUM(amount) FROM t_sale_details s WHERE m.uid=s.material_uid AND m.tenant_id=s.tenant_id) sale_amount,
      (SELECT SUM(money) FROM t_sale_details s WHERE m.uid=s.material_uid AND m.tenant_id=s.tenant_id) sale_money
      FROM t_materials m
      LEFT JOIN t_material_catalog c1 ON m.catalog_id1=c1.id
      LEFT JOIN t_material_catalog c2 ON m.catalog_id2=c2.id
      LEFT JOIN t_material_catalog c3 ON m.catalog_id3=c3.id
      LEFT JOIN t_material_catalog c4 ON m.catalog_id4=c4.id
      ${where.length > 0 ? ' WHERE ' + where.join(' AND ') : ''}
    ) a, (SELECT @idx:=0) p
    GROUP BY ${groupField || ''}`;

  const [stat] = await mysql.query(`SELECT COUNT(*) cnt,
    IFNULL(SUM(bill_count), 0) bill_count,
    IFNULL(SUM(sale_count), 0) sale_count,
    IFNULL(SUM(sale_amount), 0) sale_amount,
    IFNULL(SUM(sale_money), 0) sale_money
    FROM (${allDataSql}) a`);

  let rows = [];
  const sql = `${allDataSql}
    ${order.length > 0 ? ' ORDER BY ' + order.join(',') : ''}
    ${limit}`;
  try {
    [rows] = await mysql.query(sql);
  } catch (e) {
    console.log(e, sql);
  }

  let bill_count = 0, sale_count = 0, sale_amount = 0, sale_money = 0;
  rows.forEach(row => {
    row.bill_count = Number(row.bill_count);
    row.sale_count = Number(row.sale_count);
    row.sale_amount = Number(row.sale_amount);
    row.sale_money = Number(row.sale_money);

    bill_count += row.bill_count;
    sale_count += row.sale_count;
    sale_amount += row.sale_amount;
    sale_money += row.sale_money;
  });

  res.json({
    data: rows,
    rowsNumber: stat[0].cnt,
    summary: {
      total: {
        bill_count: Number(stat[0].bill_count),
        sale_count: Number(stat[0].sale_count),
        sale_amount: Number(stat[0].sale_amount),
        sale_money: Number(stat[0].sale_money)
      },
      page: {
        bill_count,
        sale_count,
        sale_amount,
        sale_money
      }
    }
  });
};

// 查找相似物料
export const findSimilar = async (req, res) => {
  const { data, standard } = req.body;

  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);

  const ret = await tools.findSimilarMaterial(data, standard, user.tid);
  res.json(ret);
};

// 物料信息指导入
export const doImport = async (req, res) => {
  const { data, catalog } = req.body;

  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);

  let checkResult = data.filter(v => !v.material_name.trim());
  if (checkResult.length > 0) {
    return res.json({ result: 'fail', message: `物料名称不能为空，存在${checkResult.length}条` });
  }
  checkResult = await tools.findSimilarMaterial(data, 1, user.tid);

  if (checkResult.filter(v => v.detail && v.detail.length > 0).length > 0) {
    return res.json({ result: 'fail', message: checkResult.map(v => `${v.material_name} ${v.material_model} 已存在，不允许导入`).join('<br>') });
  }

  const { c1, c2, c3, c4 } = catalog;

  let uniqueData = [];
  if (data.length > 0) {
    for (const row of data) {
      row.tenant_id = user.tid;
      row.create_time = moment().unix();
      row.create_user_id = user.id;
      row.create_user_name = user.nickname;
      row.images = [];
      row.sell = 1;
      row.describe = '';

      // 分类
      row.catalog_id1 = c1 || null;
      row.catalog_id2 = c2 || null;
      row.catalog_id3 = c3 || null;
      row.catalog_id4 = c4 || null;

      // 若无编码，则自动生成
      if (!row.material_no) {
        row.material_no = await createMaterialNo(row, user);
      }
    };

    // 过滤掉物料名称与规格重复的数据
    const uniqueDataMap = new Map();
    data.forEach(item => {
      const key = `${item.material_name || ''}-${item.material_model || ''}`;
      if (!uniqueDataMap.has(key)) {
        uniqueDataMap.set(key, item);
      }
    });
    uniqueData = Array.from(uniqueDataMap.values());

    await models.t_materials.bulkCreate(uniqueData);

    logs.recordLogs(user, { type: 'import', model: modelLabel, content: { count: data.length, data } }, req);
  }

  let message = `成功导入${uniqueData.length}条数据`;
  if (uniqueData.length !== data.length) {
    message += `，过滤掉${data.length - uniqueData.length}条重复数据`;
  }
  res.json({ result: 'ok', message });
};

// 分类列表
export const catalogList = async (req, res) => {
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);

  const where = [`tenant_id = ${user.tid}`];

  const [rows] = await mysql.query(`SELECT id, parent_id,
    CONCAT(catalog_code, ' ',catalog_name) label, catalog_code, catalog_name, levels, code_rule, IF(ISNULL(code_rule), '', 'rule') body,
    sell, sort
    FROM (
      SELECT id ,parent_id,catalog_code, catalog_name, code_rule, sell, sort,
      @le:= IF (parent_id = 0 ,1,
        IF( LOCATE( CONCAT('|',parent_id,':'),@pathlevel) > 0  ,
              SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',parent_id,':'),-1),'|',1) +1
        ,@le+1) ) levels
      , @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel
        , @pathnodes:= IF( parent_id=0,',0',
          CONCAT_WS(',',
          IF( LOCATE( CONCAT('|',parent_id,':'),@pathall) > 0  ,
            SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',parent_id,':'),-1),'|',1)
            ,@pathnodes ) ,parent_id) )paths
      ,@pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall
      FROM t_material_catalog,
      (SELECT @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vv
      WHERE ${where.join(' AND ')}
    ) src
    ORDER BY CONCAT(paths,',',id)`);

  const rootNode = rows[0]; // 根结点
  const treeNodes = rows.slice(1); // 删除根结点后

  const root = tools.treeStructure(
    rootNode,
    treeNodes,
    { name: 'sort', type: 'num' });

  res.json({ catalog: [root] });
};

// 分类详情
export const catalogDetail = async (req, res) => {
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);

  const where = { tenant_id: user.tid };

  const row = await models.t_material_catalog.findOne({ where });
  res.json(row);
};

/**
 * 货物分类新增
 */
export const catalogCreate = async (req, res) => {
  const { formData } = req.body;
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  // 设置排序号
  const cnt = await models.t_material_catalog.count({ where: { parent_id: formData.parent_id, tenant_id: user.tid } });
  formData.sort = cnt + 1;

  const ret = await singleUtils.createData('t_material_catalog', '货物分类', menuName, req, true);

  res.json(ret);
};

/**
 * 货物分类更新
 */
export const catalogUpdate = async (req, res) => {
  const ret = await singleUtils.updateData('t_material_catalog', '货物分类', menuName, req, true);

  res.json(ret);
};

/**
 * 货物分类删除
 */
export const catalogDestroy = async (req, res) => {
  // 删除前置检查
  const checkModels = [
    async (id) => {
      const [rows] = await mysql.query(`SELECT COUNT(*) cnt
        FROM t_materials
        WHERE catalog_id1=? OR catalog_id2=? OR catalog_id3=? OR catalog_id4=?`, [id, id, id, id]);
      if (rows[0].cnt > 0) {
        return `该分类下已有${rows[0].cnt}条货物档案，请清空分类货物档案后再删除`;
      } else {
        return null;
      }
    },
    async (id) => {
      const [rows] = await mysql.query(`SELECT COUNT(*) cnt
        FROM t_material_catalog
        WHERE parent_id=?`, [id]);
      if (rows[0].cnt > 0) {
        return `该分类下已有${rows[0].cnt}个子分类，请删除子分类后再删除`;
      } else {
        return null;
      }
    }
  ];

  const promises = checkModels.map(fn => fn(req.body.id));
  const results = await Promise.all(promises);

  // 处理结果
  const checkResult = results.filter(v => !!v);
  if (checkResult.length > 0) {
    return res.json({ result: 'fail', message: checkResult.join('<br>') });
  }

  const ret = await singleUtils.destroyData('t_material_catalog', '货物分类', menuName, req, true);

  res.json(ret);
};
